package com.laivi.basic.module.sql;

import java.util.List;
import java.util.Map;

import com.laivi.basic.model.datastruct.ValueMap;
import com.laivi.basic.model.sql.BasicDB;
import com.laivi.basic.model.sql.Join;
import com.laivi.basic.model.sql.Page;
import com.laivi.basic.type.DBType;
import com.laivi.basic.util.DataUtil;

public abstract class ADBDao implements DBDao {
	protected BasicDB db;

	@Override
	public List<ValueMap> query(String table, String field, String where,
			String order) {
		return query(this.getSql(table, field, where, order));
	}
	
	@Override
	public List<ValueMap> query(String joinSql,String where,String order){
		return query(this.getSql(joinSql, where, order));
	}
	
	@Override
	public List<ValueMap> query(String joinSql,Map<String,Object> where,String order){
		return query(this.getSql(joinSql, this.mapToWhere(where), order));
	}
	
	@Override
	public Page<ValueMap> query(String joinSql,String where,String order,int pageNum,int pageSize){
		return query(this.getSql(joinSql, where, order),pageNum,pageSize);
	}
	
	@Override
	public Page<ValueMap> query(String joinSql,Map<String,Object> where,String order,int pageNum,int pageSize){
		return query(this.getSql(joinSql, this.mapToWhere(where), order),pageNum,pageSize);
	}
	
	@Override
	public List<ValueMap> query(Join join, String where, String order) {
		if(!DataUtil.isEmpty(join.toWhere())){
			if(!DataUtil.isEmpty(where)){
				where+=" and "+join.toWhere();
			}else{
				where=join.toWhere();
			}
		}
		return query(join.toJoin(),where,order);
	}

	@Override
	public List<ValueMap> query(Join join, Map<String, Object> where,
			String order) {
		return query(join,this.mapToWhere(where),order);
	}

	@Override
	public Page<ValueMap> query(Join join, String where, String order,
			int pageNum, int pageSize) {
		if(!DataUtil.isEmpty(join.toWhere())){
			if(!DataUtil.isEmpty(where)){
				where+=" and "+join.toWhere();
			}else{
				where=join.toWhere();
			}
		}
		return query(join.toJoin(),where,order,pageNum,pageSize);
	}

	@Override
	public Page<ValueMap> query(Join join, Map<String, Object> where,
			String order, int pageNum, int pageSize) {
		return query(join,this.mapToWhere(where),order,pageNum,pageSize);
	}

	@Override
	public Page<ValueMap> query(String table, String field, String where,
			String order,int pageNum,int pageSize) {
		return query(this.getSql(table, field, where, order),pageNum,pageSize);
	}
	
	@Override
	public List<ValueMap> query(String table, String field,Map<String,Object> where,
			String order) {
		return this.query(table, field, this.mapToWhere(where), order);
	}
	
	@Override
	public Page<ValueMap> query(String table, String field,Map<String,Object> where,
			String order,int pageNum,int pageSize) {
		return this.query(table, field, this.mapToWhere(where), order,pageNum,pageSize);
	}

	@Override
	public Page<ValueMap> query(String sql, int pageNum, int pageSize) {
		List<ValueMap> value= query(this.getBreakSql(sql, pageNum, pageSize));
		return new Page<ValueMap>(value,count(sql),pageNum,pageSize);
	}
	
	@Override
	public List<ValueMap> query(String sql){
		return queryParam(sql);
	}
	
	@Override
	public int count(String table, String where) {
		Object value=fun(table,where,"count",null);
		if(value!=null){
			return Integer.parseInt(String.valueOf(value));
		}
		return 0;
	}
	
	@Override
	public int count(String table, Map<String,Object> where) {
		return this.count(this.mapToWhere(where));
	}

	@Override
	public int count(String sql) {
		return countParam(sql);
	}
	
	@Override
	public int countParam(String sql,Object...objs) {
		/*String temp="select count(*) as valueNum "+sql.substring(sql.indexOf(" from "), sql.length());
		if(temp.indexOf(" order by ")!=-1){
			temp=temp.substring(0, temp.indexOf(" order by"));
		}*/
		StringBuffer sb=new StringBuffer("select  count(*) as valueNum from (");
		if(sql.indexOf("order by")!=-1){
			sb.append(sql.substring(0,sql.indexOf("order by")));
		}else{
			sb.append(sql);
		}
		
		sb.append(") x");
		ValueMap value=getParam(sb.toString(),objs);
		if(value!=null && !DataUtil.isNull(value.get("valueNum"))){
			return Integer.parseInt(String.valueOf(value.get("valueNum")));
		}
		return 0;
	}

	@Override
	public Object fun(String table, String where, String fun, String field) {
		if(DataUtil.isEmpty(field)){
			field="*";
		}
		ValueMap value=get(this.getSql(table, fun+"("+field+") as valueNum", where, null));
		if(value!=null){
			return value.get("valueNum");
		}
		return null;
	}
	
	@Override
	public Object fun(String table, Map<String,Object> where, String fun, String field) {
		return this.fun(table, this.mapToWhere(where), fun, field);
	}

	@Override
	public ValueMap get(String table, String field, String where, String order) {
		return get(this.getSql(table, field, where, order));
	}
	
	@Override
	public ValueMap get(String table, String field, Map<String,Object> where, String order) {
		return get(this.getSql(table, field, this.mapToWhere(where), order));
	}
	
	@Override
	public ValueMap get(String joinSql, String where, String order) {
		return get(this.getSql(joinSql, where, order));
	}
	
	@Override
	public ValueMap get(String joinSql, Map<String,Object> where, String order) {
		return get(this.getSql(joinSql, this.mapToWhere(where), order));
	}
	
	@Override
	public ValueMap get(String sql){
		return getParam(sql);
	}
	
	@Override
	public DBType getDBType(){
		return db.getType();
	}
	
	@Override
	public ValueMap getParam(String sql,Object...objs){
		List<ValueMap> values=queryParam(sql,objs);
		if(values!=null&& values.size()>0){
			return values.get(0);
		}
		return null;
	}
	
	@Override
	public void update(String table,ValueMap value,Map<String,Object> where){
		this.update(table, value, this.mapToWhere(where));
	}

	@Override
	public void delete(String table, String where) {
		StringBuffer sb=new StringBuffer("delete from "+ table);
		if(!DataUtil.isEmpty(where)){
			sb.append(" where "+where);
		}
		execute(sb.toString());
	}
	
	@Override
	public void delete(String table, Map<String,Object> where) {
		delete(table,this.mapToWhere(where));
	}

	@Override
	public String getSql(String table, String field, String where, String order) {
		if(DataUtil.isEmpty(field)){
			field="*";
		}
		StringBuffer sb=new StringBuffer("select "+field+" from "+table);
		if(!DataUtil.isEmpty(where)){
			sb.append(" where "+where);
		}
		if(!DataUtil.isEmpty(order)){
			sb.append(" order by "+order);
		}
		return sb.toString();
	}
	
	@Override
	public String getSql(String joinSql, String where, String order) {
		StringBuffer sb=new StringBuffer(joinSql);
		if(!DataUtil.isEmpty(where)){
			sb.append(" where "+where);
		}
		if(!DataUtil.isEmpty(order)){
			sb.append(" order by "+order);
		}
		return sb.toString();
	}

	@Override
	public String getBreakSql(String sql, int pageNum, int pageSize) {
		StringBuffer sb=new StringBuffer();
		String order=null;
		switch(db.getType()){
			case SQLSERVER:
				if(sql.indexOf(" order by")!=-1){
					order=sql.substring(sql.indexOf(" order by")+9, sql.length());
				}
				sb.append("select * from (SELECT ROW_NUMBER() OVER(");
				if (DataUtil.isEmpty(order)) {
					order = "order by id";
				} else {
					sb.append("order by ");
				}
				sb.append(order+") AS rownum,t.* FROM ");
				if(sql.indexOf(" order by")!=-1){
					sb.append("("+sql.substring(0, sql.indexOf(" order by"))+") t");
				}else{
					sb.append("("+sql+") t");
				}
				sb.append(")x where x.rownum >" + ((pageNum - 1) * pageSize)+ " and x.rownum <=" + pageNum * pageSize);
				break;
			case ORACLE:
				sb.append("SELECT * FROM (SELECT ROWNUM RN,x.* FROM (");
				sb.append(sql);
				sb.append(")x WHERE ROWNUM <="+(pageNum * pageSize));
				sb.append(") WHERE RN > "+((pageNum-1) * pageSize));
				break;
			default:
				sb.append(sql);
				sb.append(" limit " + (pageNum - 1) * pageSize + "," + pageSize);
				break;
		}
		return sb.toString();
	}
	
	@Override
	public String mapToWhere(Map<String,Object> whereMap,boolean isLike){
		StringBuffer sb=new StringBuffer();
		for(Map.Entry<String, Object>entry:whereMap.entrySet()){
			if(!DataUtil.isNull(entry.getValue())){
				if(entry.getKey().indexOf("startDate")!=-1){
					sb.append(entry.getKey()+" >='"+entry.getValue()+"' and ");
				}else if(entry.getKey().indexOf("endDate")!=-1){
					sb.append(entry.getKey()+" <='"+entry.getValue()+"' and ");
				}else if(entry.getKey().indexOf("id")!=-1){
					String value=String.valueOf(entry.getValue());
					if(!"0".equals(value)){
						sb.append(entry.getKey()+" in ("+value+") and ");
					}
				}else if(entry.getKey().indexOf("=")!=-1){
					sb.append(entry.getKey()+entry.getValue()+" and ");
				}else if(entry.getKey().indexOf("(")!=-1){
					sb.append(entry.getValue()+" and ");
				}else{
					if(isLike){
						sb.append(entry.getKey()+" like '%"+entry.getValue()+"%' and ");
					}else{
						sb.append(entry.getKey()+" ='"+entry.getValue()+"' and ");
					}
				}
			}
		}
		if(sb.length()==0){
			return null;
		}
		return sb.substring(0, sb.length()-4);
	}
	
	public String mapToWhere(Map<String,Object> whereMap){
		return this.mapToWhere(whereMap, true);
	}
		
}
